﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using AspNetCs.Model;
using AspNetCs.IDAL;
using AspNetCs.Common;
using AspNetCs.Config;
using System.Data.SqlClient;
using System.Collections;

namespace AspNetCs.SQLServerDAL
{
    public class AncestralHall_DAL : IAncestralHall
    {
        /// <summary>
        /// 生成宗祠表中不存在的随机数
        /// </summary>
        /// <param name="list">不允许出现的随机数</param>
        /// <returns></returns>
        public string RndNum(ArrayList list, int Rndlen)
        {
            string Rnd = StringHelper.RndNum(Rndlen);
            for (int i = 0; i < list.Count; i++)
            {
                if (Rnd == list[i].ToString())
                {

                    Rnd = StringHelper.RndNum(Rndlen);
                    i = -1;
                }
            }
            db Conn = new db();
            try
            {

                int IsRndObj = (int)Conn.GetOneFile("Select  Count(AncestralHallId) from  AncestralHall  where  AncestralHallId='" + Rnd + "'");
                if (IsRndObj != 0)
                {
                    Rnd = RndNum(list, Rndlen);
                }

            }
            catch (Exception Ex)
            {
                new CustomException(Ex);
                Rnd = "0";
            }
            finally
            {



            }

            return Rnd;
        }

        /// <summary>
        /// 是否存在祠堂编号
        /// </summary>
        /// <param name="AncestralHallId">祠堂编号</param>
        /// <returns></returns>
        public bool IsExistAncestralHallId(string AncestralHallId)
        {
            int count = 0;
            db.ParameterArry[] paras = new db.ParameterArry[1];
            paras[0].dbType = SqlDbType.VarChar;
            paras[0].parName = "@AncestralHallId";
            paras[0].objValue = AncestralHallId;

            string cmdTex = "select count(AncestralHallId) from AncestralHall where AncestralHallId=@AncestralHallId";
            db dbclass = new db();

            SqlDataReader reader = dbclass.GetSqlDataReader(cmdTex, paras);
            if (reader.Read())
            {
                count = reader.GetInt32(0);
            }
            dbclass.close();
            dbclass.Dispose();
            return count > 0 ? true : false;
        }



        /// <summary>
        /// 添加宗祠
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(AncestralHall_Model model)
        {
            int count = 0;
            db.ParameterArry[] paras = new db.ParameterArry[14];
            model.IsIntro = 0;


            paras[0].dbType = SqlDbType.VarChar;
            paras[0].parName = "@AncestralHallId";
            paras[0].objValue = model.AncestralHallId;

            paras[1].dbType = SqlDbType.VarChar;
            paras[1].parName = "@Name";
            paras[1].objValue = model.Name;

            paras[2].dbType = SqlDbType.VarChar;
            paras[2].parName = "@PreName";
            paras[2].objValue = model.PreName;

            paras[3].dbType = SqlDbType.Int;
            paras[3].parName = "@CreateId";
            paras[3].objValue = model.CreateId;

            paras[4].dbType = SqlDbType.Int;
            paras[4].parName = "@Province";
            paras[4].objValue = model.Province;

            paras[5].dbType = SqlDbType.Int;
            paras[5].parName = "@City";
            paras[5].objValue = model.City;

            paras[6].dbType = SqlDbType.Text;
            paras[6].parName = "@Introduction";
            paras[6].objValue = model.Introduction;

            paras[7].dbType = SqlDbType.Int;
            paras[7].parName = "@MusicId";
            paras[7].objValue = model.MusicId;

            paras[8].dbType = SqlDbType.Int;
            paras[8].parName = "@TempleId";
            paras[8].objValue = model.TempleId;

            paras[9].dbType = SqlDbType.DateTime;
            paras[9].parName = "@CreateTime";
            paras[9].objValue = model.CreateTime;

            paras[10].dbType = SqlDbType.Int;
            paras[10].parName = "@IsCheck";
            paras[10].objValue = model.IsCheck;

            paras[11].dbType = SqlDbType.Int;
            paras[11].parName = "@IsDel";
            paras[11].objValue = model.IsDel;

            paras[12].dbType = SqlDbType.Int;
            paras[12].parName = "@AcceeCount";
            paras[12].objValue = model.AcceeCount;

            paras[13].dbType = SqlDbType.Int;
            paras[13].parName = "@IsIntro";
            paras[13].objValue = model.IsIntro;

            string cmdText = "insert into AncestralHall values({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13}) SELECT @@IDENTITY";
            cmdText = string.Format(cmdText, paras[0].parName, paras[1].parName, paras[2].parName, paras[3].parName, paras[4].parName, paras[5].parName, paras[6].parName, paras[7].parName, paras[8].parName, paras[9].parName, paras[10].parName, paras[11].parName, paras[12].parName, paras[13].parName);
            db dbclass = new db();
            SqlDataReader reader = dbclass.GetSqlDataReader(cmdText, paras);
            if (reader.Read())
            {
                count = Convert.ToInt32(reader[0].ToString());
            }
            return count;
        }

        /// <summary>
        /// 修改宗祠
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(AncestralHall_Model model)
        {
            int count = 0;
            db.ParameterArry[] paras = new db.ParameterArry[14];

            paras[0].dbType = SqlDbType.VarChar;
            paras[0].parName = "@AncestralHallId";
            paras[0].objValue = model.AncestralHallId;

            paras[1].dbType = SqlDbType.VarChar;
            paras[1].parName = "@Name";
            paras[1].objValue = model.Name;

            paras[2].dbType = SqlDbType.VarChar;
            paras[2].parName = "@PreName";
            paras[2].objValue = model.PreName;

            paras[3].dbType = SqlDbType.Int;
            paras[3].parName = "@CreateId";
            paras[3].objValue = model.CreateId;

            paras[4].dbType = SqlDbType.Int;
            paras[4].parName = "@Province";
            paras[4].objValue = model.Province;

            paras[5].dbType = SqlDbType.Int;
            paras[5].parName = "@City";
            paras[5].objValue = model.City;

            paras[6].dbType = SqlDbType.Text;
            paras[6].parName = "@Introduction";
            paras[6].objValue = model.Introduction;


            paras[7].dbType = SqlDbType.Int;
            paras[7].parName = "@MusicId";
            paras[7].objValue = model.MusicId;

            paras[8].dbType = SqlDbType.Int;
            paras[8].parName = "@TempleId";
            paras[8].objValue = model.TempleId;

            paras[9].dbType = SqlDbType.DateTime;
            paras[9].parName = "@CreateTime";
            paras[9].objValue = model.CreateTime;

            paras[10].dbType = SqlDbType.Int;
            paras[10].parName = "@IsCheck";
            paras[10].objValue = model.IsCheck;

            paras[11].dbType = SqlDbType.Int;
            paras[11].parName = "@IsDel";
            paras[11].objValue = model.IsDel;

            paras[12].dbType = SqlDbType.Int;
            paras[12].parName = "@AcceeCount";
            paras[12].objValue = model.AcceeCount;

            paras[13].dbType = SqlDbType.Int;
            paras[13].parName = "@Id";
            paras[13].objValue = model.Id;

            string cmdText = "update AncestralHall set AncestralHallId={0},Name={1},PreName={2},CreateId={3},Province={4},City={5},Introduction={6},MusicId={7},TempleId={8},CreateTime={9},IsCheck={10},IsDel={11},AcceeCount={12} where Id={13}";
            cmdText = string.Format(cmdText, paras[0].parName, paras[1].parName, paras[2].parName, paras[3].parName, paras[4].parName, paras[5].parName, paras[6].parName, paras[7].parName, paras[8].parName, paras[9].parName, paras[10].parName, paras[11].parName, paras[12].parName, paras[13].parName);
            db dbclass = new db();
            count = dbclass.ExcSql(cmdText, paras);
            return count;

        }

        /// <summary>
        /// 删除宗祠(物理删除)
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            int count = 0;
            db.ParameterArry[] paras = new db.ParameterArry[1];
            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@Id";
            paras[0].objValue = id;

            string cmdText = "delete from AncestralHall  where Id=@Id";
            db dbclass = new db();
            count = dbclass.ExcSql(cmdText, paras);

            return count;
        }

        /// <summary>
        /// 删除宗祠(逻辑删除)
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Hidden(int id)
        {
            int count = 0;
            db.ParameterArry[] paras = new db.ParameterArry[1];
            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@Id";
            paras[0].objValue = id;

            string cmdText = "update AncestralHall  set IsDel=1 where Id=@Id";
            db dbclass = new db();
            count = dbclass.ExcSql(cmdText, paras);

            return count;
        }

        /// <summary>
        /// 获得创建的宗祠
        /// </summary>
        /// <param name="CreateId">创建者编号</param>
        /// <param name="RequeryItems">查询的字段</param>
        /// <param name="IsDel">是否查询的被删除的信息 0:没有删除的 1:删除了的 2:所有的</param>
        /// <returns></returns>
        public DataTable getMyCreateAncestralHallItems(int CreateId, string RequeryItems, int IsDel)
        {
            DataTable dataTable = new DataTable();
            db.ParameterArry[] paras = new db.ParameterArry[2];
            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@CreateId";
            paras[0].objValue = CreateId;

            paras[1].dbType = SqlDbType.Int;
            paras[1].parName = "@IsDel";
            paras[1].objValue = IsDel;

            db dbclass = new db();

            string cmdText = "select {0} from AncestralHall where CreateId=@CreateId and IsDel=@IsDel order by CreateTime desc";
            cmdText = string.Format(cmdText, RequeryItems);

            dataTable = dbclass.GetSql_DataTable(cmdText, paras);
            return dataTable;
        }

        /// <summary>
        /// 通过编号获得宗祠详细信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public DataTable GetAncestralHallInfoById(int Id)
        {
            DataTable dataTable = new DataTable();
            db.ParameterArry[] paras = new db.ParameterArry[1];
            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@Id";
            paras[0].objValue = Id;

            db dbclass = new db();

            string cmdText = "select * from AncestralHall where Id=@Id";
            cmdText = string.Format(cmdText);

            dataTable = dbclass.GetSql_DataTable(cmdText, paras);
            return dataTable;
        }


        /// <summary>
        /// 设置推荐或者取消推荐
        /// </summary>
        /// <param name="Id"></param>
        /// <param name="CancleOrIntro">1表示设置推荐 0表示取消推荐</param>
        /// <returns></returns>
        public int SetIntro(int Id, int CancleOrIntro)
        {
            int count = 0;
            string cmdText = "update AncestralHall set IsIntro=@IsIntro where id=@id";
            db.ParameterArry[] paras = new db.ParameterArry[2];
            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@IsIntro";
            paras[0].objValue = CancleOrIntro;

            paras[1].dbType = SqlDbType.Int;
            paras[1].parName = "@id";
            paras[1].objValue = Id;

            db dbclass = new db();
            count = dbclass.ExcSql(cmdText, paras);

            return count;
        }

        /// <summary>
        /// 审核或者取消审核
        /// </summary>
        /// <param name="Id"></param>
        /// <param name="CancleOrIntro">1表示审核 0表示取消审核</param>
        /// <returns></returns>
        public int SetCheck(int Id, int CancleOrIntro)
        {
            int count = 0;
            string cmdText = "update AncestralHall set IsCheck=@IsCheck where id=@id";
            db.ParameterArry[] paras = new db.ParameterArry[2];
            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@IsCheck";
            paras[0].objValue = CancleOrIntro;

            paras[1].dbType = SqlDbType.Int;
            paras[1].parName = "@id";
            paras[1].objValue = Id;

            db dbclass = new db();
            count = dbclass.ExcSql(cmdText, paras);

            return count;
        }


        /// <summary>
        /// 设置背景音乐
        /// </summary>
        /// <param name="Id"></param>
        /// <param name="MusicId"></param>
        /// <returns></returns>
        public int SetMusic(int Id, int MusicId)
        {
            string cmdText = "update AncestralHall set MusicId=@MusicId where Id=@ID";
            int count = 0;
            db.ParameterArry[] paras = new db.ParameterArry[2];

            paras[0].dbType = SqlDbType.Int;
            paras[0].parName = "@MusicId";
            paras[0].objValue = MusicId;

            paras[1].dbType = SqlDbType.Int;
            paras[1].parName = "@ID";
            paras[1].objValue = Id;

            db dbclass = new db();
            count = dbclass.ExcSql(cmdText, paras);
            return count;

        }

        #region IAncestralHall 成员


        public DataTable GetDataTable(string filter)
        {
            DataTable dt = new DataTable();
            try
            {
                if (filter != "")
                {
                    filter = " where " + filter;
                }
                db Conn = new db();
                string sqlstr = "Select * from AncestralHall " + filter;
                dt = Conn.GetSql_DataTable(sqlstr);
            }
            catch
            {
                dt = null;
            }
            return dt;
        }

        public AncestralHall_Model GetModel(DataRow dr)
        {
            AncestralHall_Model md = new AncestralHall_Model();
            if (dr["id"] != null) { md.Id = StringHelper.isNumber_int(dr["id"].ToString()); }
            if (dr["AncestralHallId"] != null) { md.AncestralHallId = dr["AncestralHallId"].ToString(); }
            if (dr["Name"] != null) { md.Name = dr["Name"].ToString(); }
            if (dr["PreName"] != null) { md.PreName = dr["PreName"].ToString(); }
            if (dr["CreateId"] != null) { md.CreateId = StringHelper.isNumber_int(dr["PreName"].ToString()); }
            if (dr["Province"] != null) { md.Province = StringHelper.isNumber_int(dr["Province"].ToString()); }
            if (dr["City"] != null) { md.City = StringHelper.isNumber_int(dr["City"].ToString()); }
            if (dr["Introduction"] != null) { md.Introduction = dr["Introduction"].ToString(); }
            if (dr["MusicId"] != null) { md.MusicId = StringHelper.isNumber_int(dr["MusicId"].ToString()); }
            if (dr["TempleId"] != null) { md.TempleId = StringHelper.isNumber_int(dr["TempleId"].ToString()); }
            if (dr["CreateTime"] != null) { md.CreateTime = StringHelper.isDatetime(dr["CreateTime"].ToString()); }
            if (dr["IsCheck"] != null) { md.IsCheck = StringHelper.isNumber_int(dr["IsCheck"].ToString()); }
            if (dr["IsDel"] != null) { md.IsDel = StringHelper.isNumber_int(dr["IsDel"].ToString()); }
            if (dr["AcceeCount"] != null) { md.AcceeCount = StringHelper.isNumber_int(dr["AcceeCount"].ToString()); }
            if (dr["IsIntro"] != null) { md.IsIntro = StringHelper.isNumber_int(dr["IsIntro"].ToString()); }
            return md;
        }


        public void filterUpdate(string Upfilter, string filter)
        {
            db Conn = new db();
            if (filter + "" != "")
            {
                filter = "  where " + filter;
            }
            Conn.Execute_Noget("update AncestralHall Set " + Upfilter + " " + filter);
            Conn.close();
        }

        #endregion
    }
}
